const mysql = require('mysql2/promise');

async function updateMapAreasData () {
  const connection = await mysql.createConnection({
    host: '43.143.253.188',
    port: 3306,
    user: 'nextjs',
    password: 'yEAiRE2jcxTyRBy5',
    database: 'nextjs',
    timezone: '+08:00'
  });

  try {
    console.log('🔧 更新地图区域数据...\n');

    // 设置父级关系
    console.log('📝 设置父级关系...');

    // 设置顶级区域（魏国、蜀国、吴国等）
    console.log('  设置魏国区域...');
    await connection.execute(`
            UPDATE \`map_areas\` SET \`parent_id\` = 100 
            WHERE \`id\` IN (289, 302, 304)
        `);

    // 设置子区域
    console.log('  设置许昌子区域...');
    await connection.execute(`
            UPDATE \`map_areas\` SET \`parent_id\` = 289 
            WHERE \`id\` IN (2, 301, 487, 533)
        `);

    console.log('  设置洛阳子区域...');
    await connection.execute(`
            UPDATE \`map_areas\` SET \`parent_id\` = 302 
            WHERE \`id\` IN (285, 286, 333, 337)
        `);

    console.log('  设置长安子区域...');
    await connection.execute(`
            UPDATE \`map_areas\` SET \`parent_id\` = 304 
            WHERE \`id\` IN (303, 311, 414)
        `);

    console.log('  设置蜀国区域...');
    await connection.execute(`
            UPDATE \`map_areas\` SET \`parent_id\` = 19 
            WHERE \`id\` IN (70, 111, 117)
        `);

    console.log('  设置成都子区域...');
    await connection.execute(`
            UPDATE \`map_areas\` SET \`parent_id\` = 70 
            WHERE \`id\` IN (93, 112, 113)
        `);

    console.log('  设置南中子区域...');
    await connection.execute(`
            UPDATE \`map_areas\` SET \`parent_id\` = 111 
            WHERE \`id\` IN (52, 143, 351)
        `);

    console.log('  设置汉中子区域...');
    await connection.execute(`
            UPDATE \`map_areas\` SET \`parent_id\` = 117 
            WHERE \`id\` IN (306, 307, 308)
        `);

    console.log('  设置吴国区域...');
    await connection.execute(`
            UPDATE \`map_areas\` SET \`parent_id\` = 43 
            WHERE \`id\` IN (154, 212, 264)
        `);

    console.log('  设置建业子区域...');
    await connection.execute(`
            UPDATE \`map_areas\` SET \`parent_id\` = 154 
            WHERE \`id\` = 267
        `);

    console.log('  设置长沙子区域...');
    await connection.execute(`
            UPDATE \`map_areas\` SET \`parent_id\` = 212 
            WHERE \`id\` IN (220, 221, 222)
        `);

    console.log('  设置江陵子区域...');
    await connection.execute(`
            UPDATE \`map_areas\` SET \`parent_id\` = 264 
            WHERE \`id\` IN (137, 161, 163)
        `);

    // 设置等级信息
    console.log('\n📝 设置等级信息...');

    const levelUpdates = [
      { id: 2, level: '(1级)' },
      { id: 301, level: '(5级)' },
      { id: 487, level: '(20级)' },
      { id: 533, level: '(30级)' },
      { id: 285, level: '(40级)' },
      { id: 286, level: '(50级)' },
      { id: 333, level: '(80级)' },
      { id: 337, level: '(55级)' },
      { id: 303, level: '(90级)' },
      { id: 311, level: '(75级)' },
      { id: 414, level: '(65级)' },
      { id: 93, level: '(30级)' },
      { id: 112, level: '(40级)' },
      { id: 113, level: '(50级)' },
      { id: 52, level: '(60级)' },
      { id: 143, level: '(70级)' },
      { id: 351, level: '(80级)' },
      { id: 306, level: '(90级)' },
      { id: 307, level: '(100级)' },
      { id: 308, level: '(120级)' },
      { id: 222, level: '(130级)' },
      { id: 137, level: '(140级)' },
      { id: 161, level: '(140级)' }
    ];

    for (const update of levelUpdates) {
      try {
        await connection.execute(`
                    UPDATE \`map_areas\` SET \`extension\` = ? WHERE \`id\` = ?
                `, [update.level, update.id]);
        console.log(`  ✅ 区域 ${update.id} 设置等级: ${update.level}`);
      } catch (error) {
        console.log(`  ⚠️  区域 ${update.id} 设置失败: ${error.message}`);
      }
    }

    // 验证更新结果
    console.log('\n🔍 验证更新结果...');

    const [result] = await connection.execute(`
            SELECT 
                COUNT(*) as total_areas,
                COUNT(parent_id) as areas_with_parent,
                COUNT(CASE WHEN parent_id IS NULL THEN 1 END) as top_level_areas,
                COUNT(extension) as areas_with_extension
            FROM map_areas
        `);

    console.log('📊 区域统计:');
    console.log(`  总区域数: ${result[0].total_areas}`);
    console.log(`  有父级区域数: ${result[0].areas_with_parent}`);
    console.log(`  顶级区域数: ${result[0].top_level_areas}`);
    console.log(`  有等级信息区域数: ${result[0].areas_with_extension}`);

    // 显示区域层级关系示例
    const [hierarchyResult] = await connection.execute(`
            SELECT 
                \`id\`, 
                \`name\`, 
                \`parent_id\`, 
                \`extension\`,
                CASE 
                    WHEN \`parent_id\` IS NULL THEN '顶级区域'
                    ELSE CONCAT('子区域 (父级ID: ', \`parent_id\`, ')')
                END as area_type
            FROM \`map_areas\` 
            ORDER BY \`parent_id\` IS NULL DESC, \`id\`
            LIMIT 20
        `);

    console.log('\n📊 区域层级关系示例:');
    hierarchyResult.forEach(row => {
      const name = row.extension ? `${row.name}${row.extension}` : row.name;
      console.log(`  ${row.id.toString().padStart(4)} | ${name.padEnd(20)} | ${row.area_type}`);
    });

    console.log('\n🎉 地图区域数据更新完成！');

  } catch (error) {
    console.error('❌ 更新数据失败:', error.message);
    if (error.code) {
      console.error('错误代码:', error.code);
    }
  } finally {
    await connection.end();
    console.log('\n🔌 数据库连接已关闭');
  }
}

updateMapAreasData(); 